﻿using System;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace Shuba.Data
{
    public class HO
    {
        public HO()
        {
        }

        public List<Shuba.Model.HOInfo> GetList(string value)
        {
            List<Shuba.Model.HOInfo> list = new List<Shuba.Model.HOInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT [HO_ID],[HO_NAME],[ENTRY_BY]
                                                            ,[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE]
                                                     FROM [TBL_MS_HO] WHERE HO_NAME LIKE '%' + @HO_NAME + '%' ORDER BY HO_NAME");
            db.AddInParameter(cmd, "@HO_NAME", DbType.String, value);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new Shuba.Model.HOInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public List<Shuba.Model.HOInfo> GetListApproval(string value, string UpdateBy)
        {
            List<Shuba.Model.HOInfo> list = new List<Shuba.Model.HOInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT [HO_ID],[HO_NAME],[ENTRY_BY]
                                                            ,[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[ACT_TYPE]
                                                     FROM [TBL_MS_HO_TMP] WHERE HO_NAME LIKE '%' + @HO_NAME + '%' AND (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '') ORDER BY HO_NAME");
            db.AddInParameter(cmd, "@HO_NAME", DbType.String, value);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new Shuba.Model.HOInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6)));
                }
                dr.Close();
            }
            return list;
        }

        public Shuba.Model.HOInfo Get(string id)
        {
            Shuba.Model.HOInfo ri = new Shuba.Model.HOInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT [HO_ID],[HO_NAME],[ENTRY_BY]
                                                            ,[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE]
                                                     FROM [TBL_MS_HO] WHERE HO_ID LIKE '' + @HO_ID + ''");
            db.AddInParameter(cmd, "@HO_ID", DbType.String, id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new Shuba.Model.HOInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5));
                }
                dr.Close();
            }
            return ri;
        }

        public Shuba.Model.HOInfo GetApprovalByID(string id)
        {
            Shuba.Model.HOInfo ri = new Shuba.Model.HOInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT [HO_ID],[HO_NAME],[ENTRY_BY]
                                                            ,[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[ACT_TYPE]
                                                     FROM [TBL_MS_HO_TMP] WHERE HO_ID LIKE '' + @HO_ID + ''");
            db.AddInParameter(cmd, "@HO_ID", DbType.String, id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new Shuba.Model.HOInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6));
                }
                dr.Close();
            }
            return ri;
        }

        public static bool CheckDependency(string HO_ID)
        {
            bool status = false;
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT TOP 1 * FROM TBL_MS_HO_REGION WHERE RTRIM(LTRIM(HO_ID)) = @HO_ID";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@HO_ID", DbType.String, HO_ID);
            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    status = true;
                }
                mydr.Close();
            }
            return status;
        }

        public void Save(Shuba.Model.HOInfo Ho)
        {
            Database db = DBConnection.CreateDatabase();

            string sql = @"UPDATE [TBL_MS_HO_TMP]
                               SET [HO_NAME] = @HO_NAME
                                  ,[ENTRY_BY] = @ENTRY_BY
                                  ,[ENTRY_DATE] = @ENTRY_DATE
                                  ,[UPDATE_BY] = @UPDATE_BY
                                  ,[UPDATE_DATE] = @UPDATE_DATE
                                  ,[ACT_TYPE] = @ACT_TYPE
                            WHERE HO_ID LIKE '' + @HO_ID + '';
                            IF @@ROWCOUNT = 0
                            INSERT INTO [TBL_MS_HO_TMP] ([HO_ID],[HO_NAME],[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[ACT_TYPE]) " +
                          " VALUES (@HO_ID, @HO_NAME, @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE)";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@HO_ID", DbType.String, Ho.HO_ID);
            db.AddInParameter(cmd, "@HO_NAME", DbType.String, Ho.HO_NAME);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Ho.ENTRY_BY);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Ho.ENTRY_DATE);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Ho.UPDATE_BY);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Ho.UPDATE_DATE);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, Ho.ACT_TYPE);
            db.ExecuteNonQuery(cmd);
        }

        public void Approve(string HO_ID, string ACT_TYPE)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("APPROVAL_HO");

            db.AddInParameter(cmd, "@HO_ID", DbType.String, HO_ID);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ACT_TYPE);

            db.ExecuteNonQuery(cmd);
        }

        public void Reject(string HO_ID)
        {
            Database db = DBConnection.CreateDatabase();

            string sql = String.Format("DELETE FROM TBL_MS_HO_TMP WHERE HO_ID LIKE '{0}'", HO_ID);
            DbCommand cmd = db.GetSqlStringCommand(sql);

            db.ExecuteNonQuery(cmd);
        }
    }
}
